To Create a View Joining Two Tables Together to Create a New Feature
-
Create the view.
AQU_BORNEINCENDIE_P_V
CREATE OR REPLACE VIEW AQU_BORNEINCENDIE_P_V
AS
SELECT AQU_POINT_P.OBJECTID, AQU_POINT_P.GEOMETRY,
AQU_BORNEINCENDIE_TAB.BUTEE_R, AQU_BORNEINCENDIE_TAB.BUTEE_R_M,
FROM AQU_POINT_P, AQU_BORNEINCENDIE_TAB
WHERE AQU_BORNEINCENDIE_TAB.OBJECTID = AQU_POINT_P.OBJECTID;
-
Create the disabled primary key on the view.
ALTER VIEW AQU_BORNEINCENDIE_P_V ADD PRIMARY KEY (OBJECTID) DISABLE;
-
Insert the SDO_GEOM metadata.
DELETE FROM USER_SDO_GEOM_METADATA
WHERE TABLE_NAME = 'AQU_BORNEINCENDIE_P_V';
insert into user_sdo_geom_metadata select 'AQU_BORNEINCENDIE_P_V', column_name, diminfo, srid from user_sdo_geom_metadata where table_name = 'AQU_POINT_P';
COMMIT;
-
Create instead of update trigger.
CREATE OR REPLACE TRIGGER AQU_BORNEINCENDIE_P_V_U
INSTEAD OF UPDATE ON AQU_BORNEINCENDIE_P_V
FOR EACH ROW
BEGIN
UPDATE AQU_BORNEINCENDIE_TAB
SET OBJECTID = :new.OBJECTID,
BUTEE_R = :new.BUTEE_R,
BUTEE_R_M = :new.BUTEE_R_M,
WHERE
OBJECTID = :old.OBJECTID;
UPDATE AQU_POINT_P
SET OBJECTID = :new.OBJECTID,
GEOMETRY = :new.GEOMETRY
WHERE
OBJECTID = :old.OBJECTID;
END AQU_BORNEINCENDIE_P_V_U;
/
-
Create instead of insert trigger.
CREATE OR REPLACE TRIGGER AQU_BORNEINCENDIE_P_V_I INSTEAD OF INSERT ON AQU_BORNEINCENDIE_P_V DECLARE duplicate_info EXCEPTION; PRAGMA EXCEPTION_INIT (duplicate_info, -00001); BEGIN INSERT INTO AQU_POINT_P (OBJECTID, GEOMETRY) VALUES (:new.OBJECTID, :new.GEOMETRY); INSERT INTO AQU_BORNEINCENDIE_TAB (OBJECTID, BUTEE_R, BUTEE_R_M) VALUES (:new.OBJECTID, :new.BUTEE_R, :new.BUTEE_R_M); EXCEPTION WHEN duplicate_info THEN RAISE_APPLICATION_ERROR ( num=> -20107, msg=> 'Vue borne incendie dupliqué'); END AQU_BORNEINCENDIE_P_V_I; /
-
Create instead of delete trigger.
CREATE OR REPLACE TRIGGER AQU_BORNEINCENDIE_P_V_D
INSTEAD OF DELETE ON AQU_BORNEINCENDIE_P_V
BEGIN
DELETE FROM AQU_POINT_P WHERE OBJECTID = :old.OBJECTID;
DELETE FROM AQU_BORNEINCENDIE_TAB WHERE OBJECTID = :old.OBJECTID;
END AQU_BORNEINCENDIE_P_V_D;
/
COMMIT;